/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package net.wofly.sale.report.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import net.wofly.sale.report.domain.SaleDailyReport;
import org.springframework.data.repository.query.Param;

public interface SaleDailyReportRepository
		extends PagingAndSortingRepository<SaleDailyReport, String>, JpaSpecificationExecutor<SaleDailyReport> {
	
	Page<SaleDailyReport> findByCountDate(Long countDate,Pageable pageable);
	
	List<SaleDailyReport> findByUserIDAndCountDate(String userID ,Long countDate);

	@Query(value = "SELECT AA.userid as id, u.Name as name, AA.commodityTerminalID, ct.commodityTerminalName, AA.sum_num " +
			"FROM " +
			"( " +
			" SELECT " +
			"  A.warehouseid AS userid, iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			" FROM " +
			" ( " +
			"  SELECT os.warehouseid, os.outstockid, os.modifydate " +
			"  FROM sale_stock_outstock os  " +
			"  JOIN sale_stock_warehouse wh ON os.warehouseid = wh.warehouseid " +
			"  WHERE os.modifydate BETWEEN :countStartDate AND :countEndTime AND wh.warehousetype = 3  " +
			" ) A " +
			" LEFT JOIN " +
			"  sale_stock_inoutstockdetail iosd ON iosd.operatestockid = A.outstockid " +
			" GROUP BY A.warehouseid, iosd.commodityTerminalID " +
			") AA " +
			"LEFT JOIN " +
			" sale_right_user u ON u.Userid = AA.userid " +
			"LEFT JOIN " +
			" sale_commodity_commoditytml ct ON ct.commodityTerminalID = AA.commodityTerminalID ", nativeQuery = true)
	List<List<Object>> managerOutStockSummary(@Param("countStartDate") Long countStartDate, @Param("countEndTime") Long countEndTime);

	@Query(value = "SELECT AA.warehouseid as id, w.warehousename as name, AA.commodityTerminalID, ct.commodityTerminalName, AA.sum_num " +
			"FROM " +
			"( " +
			"  SELECT " +
			"    A.warehouseid, iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			"  FROM  " +
			"  (  " +
			"    SELECT os.warehouseid, os.outstockid, os.modifydate " +
			"    FROM sale_stock_outstock os " +
			"    JOIN sale_stock_warehouse wh ON os.warehouseid = wh.warehouseid " +
			"    WHERE os.modifydate BETWEEN :countStartDate AND :countEndTime AND wh.warehousetype = 2 " +
			"  ) A " +
			"  LEFT JOIN " +
			"    sale_stock_inoutstockdetail iosd ON iosd.operatestockid = A.outstockid " +
			"  GROUP BY A.warehouseid, iosd.commodityTerminalID " +
			") AA " +
			"LEFT JOIN " +
			"  sale_stock_warehouse w ON w.warehouseid = AA.warehouseid " +
			"LEFT JOIN " +
			"  sale_commodity_commoditytml ct ON ct.commodityTerminalID = AA.commodityTerminalID", nativeQuery = true)
	List<List<Object>> storeOutStockSummary(@Param("countStartDate") Long countStartDate, @Param("countEndTime") Long countEndTime);

	@Query(value = "SELECT AA.userid as id, u.Name AS name, AA.commodityTerminalID, ct.commodityTerminalName, AA.sum_num " +
			"FROM " +
			"( " +
			" SELECT " +
			"  A.warehouseid AS userid, iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			" FROM " +
			" ( " +
			"  SELECT ins.warehouseid, ins.instockid, ins.modifydate " +
			"  FROM sale_stock_instock ins  " +
			"  JOIN sale_stock_warehouse wh ON ins.warehouseid = wh.warehouseid " +
			"  WHERE ins.modifydate BETWEEN :countStartDate AND :countEndTime AND wh.warehousetype = 3  " +
			" ) A " +
			" LEFT JOIN " +
			"  sale_stock_inoutstockdetail iosd ON iosd.operatestockid = A.instockid " +
			" GROUP BY A.warehouseid, iosd.commodityTerminalID " +
			") AA " +
			"LEFT JOIN " +
			" sale_right_user u ON u.Userid = AA.userid " +
			"LEFT JOIN " +
			" sale_commodity_commoditytml ct ON ct.commodityTerminalID = AA.commodityTerminalID", nativeQuery = true)
	List<List<Object>> managerInStockSummary(@Param("countStartDate") Long countStartDate, @Param("countEndTime") Long countEndTime);

	@Query(value="SELECT AA.warehouseid as id, w.warehousename as name, AA.commodityTerminalID, ct.commodityTerminalName, AA.sum_num " +
			"FROM " +
			"( " +
			" SELECT " +
			"  A.warehouseid, iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			" FROM " +
			" ( " +
			"  SELECT os.warehouseid, os.instockid, os.modifydate " +
			"  FROM sale_stock_instock os  " +
			"  JOIN sale_stock_warehouse wh ON os.warehouseid = wh.warehouseid " +
			"  WHERE os.modifydate BETWEEN :countStartDate AND :countEndTime AND wh.warehousetype = 2  " +
			" ) A " +
			" LEFT JOIN " +
			"  sale_stock_inoutstockdetail iosd ON iosd.operatestockid = A.instockid " +
			" GROUP BY A.warehouseid, iosd.commodityTerminalID " +
			") AA " +
			"LEFT JOIN " +
			" sale_stock_warehouse w ON w.warehouseid = AA.warehouseid " +
			"LEFT JOIN " +
			" sale_commodity_commoditytml ct ON ct.commodityTerminalID = AA.commodityTerminalID", nativeQuery = true)
	List<List<Object>> storeInStockSummary(@Param("countStartDate") Long countStartDate, @Param("countEndTime") Long countEndTime);

	@Query(value = "SELECT A.commodityTerminalID, ct.commodityTerminalName, A.sum_num " +
			"FROM " +
			"( " +
			"  SELECT " +
			"    iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			"FROM " +
			"  sale_stock_outstock os " +
			"LEFT JOIN  " +
			"  sale_stock_inoutstockdetail iosd ON iosd.operatestockid = os.outstockid " +
			"WHERE os.modifydate BETWEEN :countStartDate AND :countEndTime " +
			"GROUP BY iosd.commodityTerminalID " +
			") A " +
			"LEFT JOIN " +
			" sale_commodity_commoditytml ct ON ct.commodityTerminalID = A.commodityTerminalID", nativeQuery = true)
	List<List<Object>> commodityTerminalOutStockSummary(@Param("countStartDate") Long countStartDate, @Param("countEndTime") Long countEndTime);

	@Query(value = "SELECT A.commodityTerminalID, ct.commodityTerminalName, A.sum_num " +
			"FROM " +
			"( " +
			"  SELECT " +
			"    iosd.commodityTerminalID, SUM(IFNULL(iosd.num,0)) AS sum_num " +
			"FROM " +
			"  sale_stock_outstock os " +
			"LEFT JOIN  " +
			"  sale_stock_inoutstockdetail iosd ON iosd.operatestockid = os.outstockid " +
			"WHERE os.modifydate BETWEEN :countStartDate AND :countEndTime " +
			"AND os.warehouseid = :warehouseID " +
			"GROUP BY iosd.commodityTerminalID " +
			") A " +
			"LEFT JOIN " +
			" sale_commodity_commoditytml ct ON ct.commodityTerminalID = A.commodityTerminalID", nativeQuery = true)
	List<List<Object>> commodityTerminalOutStockSummary(@Param("warehouseID") String warehouseID, @Param("countStartDate") Long countStartDate,
														@Param("countEndTime") Long countEndTime);

	int deleteByCountDate(Long countDate);
}